package com.example.publishDemo.Mapper;

import com.example.publishDemo.Model.Article;
import com.example.publishDemo.Model.ArticleReport;
import com.example.publishDemo.Model.ArticleScore;
import com.example.publishDemo.Model.ArticleStar;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface ArticleMapper {

    @Insert("insert into articles(author,author_gravatar,author_id,title,description,tags,content,date,cover) values( #{author}, #{author_gravatar} , #{author_id}, #{title}, #{description}, #{tags}, #{content}, #{date}, #{cover})")
    int save(Article article);

//    ---------  以下是五个前端推荐需要的方法，具体使用在ArticleServiceImpl中参考

//     SELECT * FROM `articles` where 1=1 and (title like concat('%','','%') or description like concat('%','','%') or tags like concat('%','','%')) order by date desc limit 0,4 ;
    @Select({"<script>",
            "select * from articles",
            "where 1=1 ",
            "       <if test='keyword != null and keyword.trim().length() != 0'>",
            "           and (title like concat('%',#{keyword},'%') or description like concat('%',#{keyword},'%') or tags like concat('%',#{keyword},'%')) " ,
            "       </if>",
            "order by date desc limit #{start},#{end}",
            "</script>"})
    List<Article> rangeForTime(int start, int end,String keyword);
            //配套的数据
            @Select({"<script>",
                    "select count(*) from articles",
                    "where 1=1 ",
                    "       <if test='keyword != null and keyword.trim().length() != 0'>",
                    "           and (title like concat('%',#{keyword},'%') or description like concat('%',#{keyword},'%') or tags like concat('%',#{keyword},'%')) " ,
                    "       </if>",
                    "order by date desc",
                    "</script>"})
            int rangeForTimeNum(String keyword);

//     SELECT * FROM `articles` where 1=1 and (title like concat('%','','%') or description like concat('%','','%') or tags like concat('%','','%')) and a_id = 1 ;
    @Select({"<script>",
            "select * from articles",
            "where 1=1 ",
            "       <if test='keyword != null and keyword.trim().length() != 0'>" ,
            "           and (title like concat('%',#{keyword},'%') or description like concat('%',#{keyword},'%') or tags like concat('%',#{keyword},'%')) " ,
            "       </if>" ,
            "and a_id = #{integer}",
            "</script>"})
    Article findByAidAndKeyword(Integer integer,String keyword);

//     SELECT * FROM `articles` where 1=1 and (title like concat('%','','%') or description like concat('%','','%') or tags like concat('%','','%')) order by views desc limit 0,4 ;
    @Select({"<script>" ,
            "select * from articles" ,
            "where 1=1 " ,
            "       <if test='keyword != null and keyword.trim().length() != 0'>" ,
            "           and (title like concat('%',#{keyword},'%') or description like concat('%',#{keyword},'%') or tags like concat('%',#{keyword},'%')) " ,
            "       </if>" ,
            "order by views desc limit #{start},#{end}",
            "</script>"})
    List<Article> orderByViewsAndKeyword(int start, int end, String keyword);
            //配套数据
            @Select({"<script>" ,
                    "select count(*) from articles" ,
                    "where 1=1 " ,
                    "       <if test='keyword != null and keyword.trim().length() != 0'>" ,
                    "           and (title like concat('%',#{keyword},'%') or description like concat('%',#{keyword},'%') or tags like concat('%',#{keyword},'%')) " ,
                    "       </if>" ,
                    "order by views desc",
                    "</script>"})
            int orderByViewsAndKeywordNum(String keyword);

//     SELECT * FROM `articles` where 1=1 and (title like concat('%','','%') or description like concat('%','','%') or tags like concat('%','','%')) order by score desc limit 0,4 ;
    @Select({"<script>" ,
            "select * from articles" ,
            "where 1=1 " ,
            "       <if test='keyword != null and keyword.trim().length() != 0'>" ,
            "           and (title like concat('%',#{keyword},'%') or description like concat('%',#{keyword},'%') or tags like concat('%',#{keyword},'%')) " ,
            "       </if>" ,
            "order by score desc limit #{start},#{end}" ,
            "</script>"})
    List<Article> orderByScoreAndKeyword(int start, int end, String keyword);
            //配套数据
            @Select({"<script>" ,
                    "select count(*) from articles" ,
                    "where 1=1 " ,
                    "       <if test='keyword != null and keyword.trim().length() != 0'>" ,
                    "           and (title like concat('%',#{keyword},'%') or description like concat('%',#{keyword},'%') or tags like concat('%',#{keyword},'%')) " ,
                    "       </if>" ,
                    "order by score desc" ,
                    "</script>"})
            int orderByScoreAndKeywordNum(String keyword);

//    SELECT * FROM `articles` where 1=1 and (title like concat('%','','%') or description like concat('%','','%') or tags like concat('%','','%')) and author_id = 2 ;
    @Select({"<script>" ,
            "select * from articles" ,
            "where 1=1 " ,
            "       <if test='keyword != null and keyword.trim().length() != 0'>" ,
            "           and (title like concat('%',#{keyword},'%') or description like concat('%',#{keyword},'%') or tags like concat('%',#{keyword},'%')) " ,
            "       </if>" ,
            "and author_id = #{integer}",
            "</script>"})
    List<Article> findByAuthorIdAndKeyword(Integer integer, String keyword);
//    --------------------------------================-------------------------------

    @Select("select * from articles where isRecommend = 0 order by title asc limit 5")
    List<Article> forCommend();

    @Update("update articles set views = views+1 where a_id = #{flag}")
    int increaseView(int flag);

    @Update("UPDATE articles\n" +
            "SET score = ROUND((score * raters + #{flag}) / (raters + 1), 2), raters = raters + 1\n" +
            "WHERE a_id = #{a_id};")
    int averageScore(double flag,int a_id);

    @Insert("insert into articles_score(u_id,a_id,score) values( #{u_id}, #{a_id}, #{flag})")
    int saveScore(int u_id, int a_id, double flag);

    @Select("select * from articles_score where u_id = #{u_id} and a_id = #{a_id}")
    List<ArticleScore> isScore(int u_id, int a_id);

    @Select("select * from articles where a_id =#{a_id}")
    Article getOne(int a_id);

    @Insert("insert into article_star(u_id,a_id) values( #{u_id}, #{a_id})")
    int star(int u_id, int a_id);

    @Delete("Delete from article_star where u_id = #{u_id} and a_id = #{a_id}")
    int delStar(int u_id, int a_id);

    @Select("select * from article_star where u_id = #{u_id} and a_id = #{a_id}")
    List<ArticleStar> isStar(int u_id, int a_id);

    @Select("select count(*) from article_star where a_id = #{a_id}")
    int staredNum(int a_id);

    @Insert("insert into article_report(u_id,a_id) values( #{u_id},#{a_id})")
    int report(int a_id, int u_id);

    @Select("select * from article_report where a_id = #{a_id} and u_id = #{u_id}")
    ArticleReport isReport(int a_id, int u_id);

    @Select("select count(*) from articles")
    int countAllArticle();

    @Select("select COALESCE(SUM(views), 0) from articles where author_id = #{u_id}")  //确保为空时不会直接返回null
    int allViewsForId(int u_id);

    @Select("SELECT COALESCE(MAX(views), 0) FROM articles WHERE author_id = #{u_id};")
    int maxView(int u_id);

    @Select("select * from articles where author_id = #{u_id}")
    List<Article> getAllArticleForId(int u_id);

    @Update("update articles set author=#{author}," +
            "author_gravatar=#{author_gravatar}," +
            "author_id=#{author_id}," +
            "title=#{title}," +
            "description=#{description}," +
            "tags=#{tags}," +
            "content=#{content}," +
            "date=#{date}," +
            "cover=#{cover} where a_id = #{a_id}")
    int change(Article article);

    @Select("select a_id from article_star where u_id=#{u_id}")
    List<Integer> GetAllCollectArticle(int u_id);

    @Select("select * from articles where tags like concat('%',#{tag},'%')")
    List<Article> getArticleForTag(String tag);

    @Select("select count(*) from articles where tags like concat('%',#{tag},'%')")
    int articleNumByTag(String tag);

    @Update("update article_report set process = 0 where r_id = #{r_id}")
    int reReport(int r_id);

    @Update("update articles set author_gravatar = #{picture} where author_id=#{u_id}")
    void changeUserGravatar(String picture, int u_id);

    @Insert("insert into article_report(a_id,u_id) values(#{a_id},#{u_id})")
    int addReport(int a_id, int u_id);
}
